# coding: utf-8
# xlswriter.py

# http://pypi.python.org/pypi/xlwt

from xlwt import *

class XLSWriter(object):
    """A XLS writer that produces XLS files from unicode data.
    """
    def __init__(self, file, encoding='utf-8'):
        # must specify the encoding of the input data, utf-8 default.
        self.file = file
        self.encoding = encoding
        self.wbk = Workbook()
        self.sheets = {}

    #创建sheet
    def create_sheet(self, sheet_name='sheet'):
        """Create new sheet
        """
        if sheet_name in self.sheets:
            sheet_index = self.sheets[sheet_name]['index'] + 1
        else:
            sheet_index = 0
            self.sheets[sheet_name] = {'header': []}
        self.sheets[sheet_name]['index'] = sheet_index
        self.sheets[sheet_name]['sheet'] = self.wbk.add_sheet('%s%s' % (sheet_name, sheet_index if sheet_index else ''), cell_overwrite_ok=True)
        self.sheets[sheet_name]['rows'] = 1

    #添加单元格
    def cell(self, s):
        if isinstance(s, basestring):
            if not isinstance(s, unicode):
                s = s.decode(self.encoding)
        elif s is None:
            s = ''
        else:
            s = str(s)
        return s
    #设定单元格样式  可选择内容是否加粗
    def cellstyle(self,bold=True):
        #设定字体
        fnt1 = Font()
        fnt1.name = 'Verdana'
        fnt1.bold = bold

        #设定居中
        al1 = Alignment()
        al1.horz = Alignment.HORZ_CENTER
        al1.vert = Alignment.VERT_CENTER

        #设定边线
        brd1 = Borders()
        brd1.left = 0x01
        brd1.right = 0x01
        brd1.top = 0x01
        brd1.bottom = 0x01

        #定义样式
        style = XFStyle()
        style.alignment = al1
        style.font = fnt1
        style.borders = brd1
        return style

    #添加非合并的单元格
    def writerow(self, row, sheet_name='sheet'):
        if sheet_name not in self.sheets:
            self.create_sheet(sheet_name)
    
        if self.sheets[sheet_name]['rows'] >= 65534:
            self.save()
            self.create_sheet(sheet_name)
        for ci, col in enumerate(row):
            self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col) if type(col) != ExcelFormula.Formula else col,self.cellstyle(bold=False))
        self.sheets[sheet_name]['rows'] += 1

    #添加合并的单元格
    def writemergerow(self, merge_value='', sheet_name='sheet',row1=0,row2=0,column1=0,column2=0):
        if sheet_name not in self.sheets:
            # Create if does not exist
            self.create_sheet(sheet_name)

        xlswriter.sheets[sheet_name]['sheet'].write_merge(row1, row2, column1, column2, merge_value,self.cellstyle())
        xlswriter.sheets[sheet_name]['sheet'].col(1).width = 0x0d00

    #添加多行单元格
    def writerows(self, rows, sheet_name='sheet'):
        for row in rows:
            self.writerow(row, sheet_name)

    #保存文件
    def save(self):
        self.wbk.save(self.file)
#测试数据
if __name__ == '__main__':
    # test
    xlswriter = XLSWriter(u'长沙.xls')
    xlswriter.writemergerow(merge_value= u'日期',sheet_name=u'基本信息',row1=0, row2=0, column1=0, column2=0)
    xlswriter.writemergerow(merge_value= '2013-12',sheet_name=u'基本信息',row1=0, row2=0, column1=1, column2=1)
    xlswriter.writemergerow(merge_value= '2013-12-01',sheet_name=u'基本信息',row1=0, row2=0, column1=2, column2=4)
    xlswriter.writerow(['终端名', '在线/次数', '早', '中', '晚'], sheet_name=u'基本信息')
    xlswriter.writerow(['TRM01', '10/93', 'V', 'V', 'X'], sheet_name=u'基本信息')
    xlswriter.writerow(['TRM02', '10/93', 'V', 'V', 'X'], sheet_name=u'基本信息')
    xlswriter.writerow(['TRM03', '10/93', 'V', 'V', 'X'], sheet_name=u'基本信息')

    # don't forget to save data to disk
    xlswriter.save()
    print 'finished.'
